---
description: The `SubqueryOne` and `SubqueryMany` clauses can be used to include nested queries in a Triplit query.
---

import { Callout } from 'nextra/components';

# Subqueries

Subqueries can be used to add an ad-hoc query on related data to an entity. [Relations](/schemas/relations) are formalized subqueries that are defined in the schema. You can use the `SubqueryOne` and `SubqueryMany` builder methods to to add any nested query to a Triplit query at runtime, regardless of relations in the schema.

For example, the following schema has two collections, `users` and `blogs`, where each blog post has an `author` attribute that references a user:

```typescript
const schema = S.Collections({
  users: S.Schema({
    id: S.Id(),
    name: S.String(),
  }),
  blogs: S.Schema({
    id: S.Id(),
    title: S.String(),
    text: S.String(),
    author: S.String(),
    created_at: S.Date({ default: S.Default.now() }),
  }),
});
```

## `SubqueryMany`

To query all blogs with their associated user, you can use the `SubqueryMany` method:

```typescript
const query = client.query('users').SubqueryMany(
  'userBlogs', // key
  client // query
    .query('blogs')
    .Where(['author', '=', '123'])
    .Select(['title', 'text'])
);

/*
A given entity in the result will look like this:
{
  id: '123',
  name: 'Alice',
  userBlogs: [
    { title: 'My first blog', text: 'Hello world!' },
    { title: 'My second blog', text: 'Goodbye world!' },
  ],
}
*/
```

The return value of the subquery stored at the `userBlogs` key in each entity will be a nested array of blog items.

## `SubqueryOne`

`SubqueryOne` is like `SubqueryMany`, but will return the subquery's first match. Instead of a full nested array of results, the key where the `SubqueryOne` stores it results will either be the single result or `null`. The following query will return the text of the most recent blog item created by the user:

```typescript
const query = client
  .query('users')
  .SubqueryOne(
    'mostRecentBlog',
    client
      .query('blogs')
      .Select(['text'])
      .Where(['author', '=', '123'])
      .Order('created_at', 'DESC')
      .Limit(1)
  );

/*
A given entity in the result will look like this:
{
  id: '123',
  name: 'Alice',
  mostRecentBlog: { text: 'Hello world!' },
}
*/
```

## Use with relational queries

The examples above are hardcoded to a specific user ID, however you can use [referential variables](/query/variables#referential-variables) to make the subquery dynamic. For example:

```typescript
// Fetch all 747 planes that have a flight to an airport newer than the plane
const query = client.query('planes').Where([
  ['model', '=', '747']
  {
    exists: client.query('flights').Where([
      ['plane_id', '=', '$1.id'],
      {
        exists: client.query('airports').Where([
          ['id', '=', '$1.destination_id']
          ['created_at', '>', '$2.created_at'],
        ]),
      },
    ]),
  },
]);
```
